<?php
// create custom plugin settings menu
add_action('admin_menu', 'conversionreport_create_menu');

function conversionreport_create_menu() {

	//create new dashboard menu page
	add_dashboard_page('Conversion Rates Report', 'Conversion Rates Report', 'administrator', __FILE__, 'conversionreport_settings_page', 'favicon.ico', '4');
        
	//call register settings function
	add_action( 'admin_init', 'register_productconversionreportsettings' );
}

function register_productconversionreportsettings() {
	//register our settings
	register_setting( 'conversionreport-settings-group', 'conversionreport_tracking_code' );
}

function conversionreport_settings_page() {
?>

<div class="wrap">
<h2>Product Conversion Rate Report</h2>

<?php // <p><a class="button" href="#" id="exportToExcel">Export To Xecel</a></p> ?>
<div id="dvData">
<table class="conversiontable" id="ReportTable"  style="width:100%;text-align:left;" cellpadding="0" cellspacing="0">
    <tr>
        <th>View / Edit</th>
        <th>ID</th>
        <th>SKU</th>
        <th>Name</th>
        <th style="text-align:center;">Views</th>
        <th style="text-align:center;">Sales</th>
        <th style="text-align:center;">Conversion Rate</th>
    </tr
<?php

   $args = array('post_type' => 'product', 'posts_per_page' => '-1', 'orderby' => 'meta_value_num', 'meta_key' => 'post_views_count');
   $productlist = new WP_Query($args);

   if($productlist->have_posts()) : 
      while($productlist->have_posts()) : 
         $productlist->the_post();

    $productname = get_the_title();
    $postviewcount = getPostViews(get_the_ID());
    
    // Call The Product SKU For Display
	$productsku = get_post_custom($post->ID);
	$showsku = $productsku['_sku'];
	foreach ( $showsku as $mainsku );
    
    global $wpdb;
    $productorders = $wpdb->query("SELECT * FROM wp_woocommerce_order_items WHERE order_id > '28525' AND order_item_name LIKE '%".$productname."%'");
    $productordercount = $wpdb->num_rows;
    
    if($postviewcount != '0') {
        $productconversion = $productordercount / $postviewcount;
        $productconversion = $productconversion * 100;
    } else {
        $productconversion = '0';	
    }
   
?>

        <tr>
            <td style="padding-top:5px;padding-bottom:5px;border-bottom:#cccccc 1px solid;"><a href="<?php the_permalink();?>" target="_blank">V</a> | <a href="http://www.siliconsolar.com/wp-admin/post.php?post=<?php the_ID(); ?>&action=edit" target="_blank">E</a></td>
            <td style="padding-top:5px;padding-bottom:5px;padding-right:10px;border-bottom:#cccccc 1px solid;"><?php the_ID(); ?></td>
            <td style="padding-top:5px;padding-bottom:5px;border-bottom:#cccccc 1px solid;"><?php echo $mainsku ; ?></td>
            <td style="padding-top:5px;padding-bottom:5px;border-bottom:#cccccc 1px solid;max-width:40%;"><?php the_title(); ?></td>
            <td style="text-align:center;padding-top:5px;padding-bottom:5px;border-bottom:#cccccc 1px solid;"><?php echo getPostViews(get_the_ID()); ?></td>
            <td style="text-align:center;padding-top:5px;padding-bottom:5px;border-bottom:#cccccc 1px solid;"><?php echo $productordercount ; ?></td>
            <td style="text-align:center;padding-top:5px;padding-bottom:5px;border-bottom:#cccccc 1px solid;"><?php echo round($productconversion,2); ?> %</td>
        </tr>

<?php
      endwhile; wp_reset_query();
   else: 
?>

      <p>Oops, there are no errors listed</p>

<?php
   endif;
?>
      
</table>
</div>

</div>
<?php } ?>